<%@page contentType="application/pdf"  language="java" import="java.sql.*,java.text.*,java.util.*,java.io.FileOutputStream,java.io.IOException,com.lowagie.text.*,com.lowagie.text.pdf.PdfWriter,java.awt.Color,java.io.ByteArrayOutputStream"%><%@include file="../../Connections/aranciaDB0.jsp"%><%@include file="../../utils/getMessagesPdf.jsp"%><%

//---------Variables Globales--------------------------------------------------------------
//Dias en cada mes dias[enero] = 31
int dia[] = {31,28,31,30,31,31,30,31,30,31,30,31};
//lee las variables de request
String bancoid = request.getParameter("bancoID");
String cuentaid = request.getParameter("cuentaID");
String anyo = request.getParameter("anyo");
String mes = request.getParameter("mes");
String tipo = request.getParameter("tipo");
SimpleDateFormat sdf = new SimpleDateFormat( "dd/MMM/yyyy" );
String title = "";
switch( (new Integer(tipo)).intValue() ){
	case 1:title = rr[10];
		break;
	case 2:title = rr[11];
		break;
	case 3:title = rr[12];
		break;
	case 4:title = rr[13];
		break;
}

response.setContentType("application/pdf");
response.setHeader("Expires", "0");
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");


//String de select
String sqlQuery[] = new String[8];
sqlQuery[2] = 
	" select   ARB2_TFGLD106.recordid," +
	"          T_DCDT as fecha, "+
	"	       T_AMNT as importe, "+
	"	       isnull( comentarios, '' ) as observaciones, "+
	"		   case when len(T_REFR) > 38 then substring(T_REFR, 0, 35) + '...' else rtrim(ltrim(T_REFR)) end as referencia, "+
	"          1 as tipo "+
	" from 	   ARB2_TFGLD106, ARB2_CUENTAS  "+
	" where    ARB2_CUENTAS.cuentaBaan = ARB2_TFGLD106.T_LEAC AND "+
	"		   ARB2_CUENTAS.divisa = ARB2_TFGLD106.T_CCUR AND "+
	"          T_DBCR = 2 and  "+
	"	       T_LEAC in (select cuentabaan from arb2_cuentas where recordid="+cuentaid+") and  "+
	"	       T_FYER = "+anyo+" AND   "+
	"	       T_FPRD = "+mes+" and  "+
	"          T_AMNT <> 0 and "+
	"		   ARB2_TFGLD106.T_DCDT >= ARB2_CUENTAS.INIOPERACION AND "+
	"	       ARB2_TFGLD106.idtxconcilia is null "+
	" union all "+
	" SELECT  -1, "+
	"	      convert( datetime, convert( varchar, case when month(t_dcdt) = 12 then year(t_dcdt) + 1 else year(t_dcdt) end ) + '/' + convert( varchar, case when month(t_dcdt) = 12 then 1 else (month(t_dcdt)+1) end ) + '/01' )-1, "+
	"	      abs( dbo.fnc_totalDetalleConciliacion( dc.idtxconcilia, 'arb2_tfgld106' ) - dbo.fnc_totalDetalleConciliacion( dc.idtxconcilia, 'arb2_transacciones' ) ),  "+
	"	       isnull( dc.comentario, '' ) as observaciones, "+
	"	      'Concil Parcial ' + convert( varchar, dc.idtxconcilia ) + ' ' + convert( varchar, month(t_dcdt) ) + '/' + convert( varchar, year( t_dcdt ) ), "+
	"          0 as tipo "+
	" FROM 	  arb2_DETALLEconciliaciones dc  "+
	"		  inner join arb2_tfgld106 t "+
	"		  on t.recordid = ( "+
	"			 select	top 1 recordid "+
	"			 from 	arb2_tfgld106 "+
	"			 where	idtxconcilia = dc.idtxconcilia "+
	"		  ) "+
	" WHERE   dc.status = 0 AND  "+
	"	      dc.baanbanco = 2 and "+
	"	      dc.tipotransaccion = 1 and "+
	"	      dc.conciliacionid = (select conciliacionid from arb2_conciliaciones where cuentaid = "+cuentaid+" and mes = "+mes+" and anyo = "+anyo+") "+
	" order by 6 desc, 2 ";
sqlQuery[1] = 
	" select  ARB2_TRANSACCIONES.recordid," +
	"         fecha, "+
	"	      importe, "+
	"	       isnull( comentarios, '' ) as observaciones, "+
	"	      case when len(referencia) > 38 then substring(referencia, 0, 35) + '...' else referencia end as referencia, "+
	"          1 as tipo "+
	" from    ARB2_TRANSACCIONES, ARB2_CUENTAS   "+
	" where   tipo_transaccion = 1 and  "+
	"		  ARB2_CUENTAS.RECORDID = ARB2_TRANSACCIONES.CUENTAID AND "+
	"	      cuentaid = "+cuentaid+" and  "+
	"	      anyo = "+anyo+" AND   "+
	"	      periodo = "+mes+" and  "+
	"         importe <> 0 and "+
	"		  ARB2_TRANSACCIONES.FECHA >= ARB2_CUENTAS.INIOPERACION AND "+
	"	      ARB2_TRANSACCIONES.idtxconcilia is null "+
	" union all "+
	" SELECT  -1, "+
	"         convert( datetime, convert( varchar, case when month(fecha) = 12 then year(fecha) + 1 else year(fecha) end ) + '/' + convert( varchar, case when month(fecha) = 12 then 1 else (month(fecha)+1) end ) + '/01' )-1, "+
	"	      abs( dbo.fnc_totalDetalleConciliacion( dc.idtxconcilia, 'arb2_transacciones' ) - dbo.fnc_totalDetalleConciliacion( dc.idtxconcilia, 'arb2_tfgld106' ) ),  "+
	"	       isnull( dc.comentario, '' ) as observaciones, "+
	"	      'Concil Parcial ' + convert( varchar, dc.idtxconcilia ) + ' ' + convert( varchar, month(fecha) ) + '/' + convert( varchar, year( fecha ) ), "+
	"         0 as tipo "+
	" FROM 	  arb2_DETALLEconciliaciones dc  "+
	"		  inner join arb2_transacciones t "+
	"		  on t.recordid = ( "+
	"			 select	top 1 recordid "+
	"			 from 	arb2_transacciones "+
	"			 where	idtxconcilia = dc.idtxconcilia "+
	"		  ) "+
	" WHERE   dc.status = 0 AND  "+
	"	      dc.baanbanco = 1 and "+
	"	      dc.tipotransaccion = 1 and "+
	"	      dc.conciliacionid = (select conciliacionid from arb2_conciliaciones where cuentaid = "+cuentaid+" and mes = "+mes+" and anyo = "+anyo+") "+
	" order by 6 desc, 2 ";
sqlQuery[0] = 
	" select   ARB2_TFGLD106.recordid," +
	"          T_DCDT as fecha, "+
	"	       T_AMNT as importe, "+
	"	       isnull( comentarios, '' ) as observaciones, "+
	"		   case when len(T_REFR) > 38 then substring(T_REFR, 0, 35) + '...' else rtrim(ltrim(T_REFR)) end as referencia, "+
	"          1 as tipo "+
	" from 	   ARB2_TFGLD106, ARB2_CUENTAS  "+
	" where    T_DBCR = 1 "+
	"           and ARB2_CUENTAS.cuentaBaan = ARB2_TFGLD106.T_LEAC AND "+
	"		   ARB2_CUENTAS.divisa = ARB2_TFGLD106.T_CCUR AND "+
	"	       T_LEAC in (select cuentabaan from arb2_cuentas where recordid="+cuentaid+") and  "+
	"	       T_FYER = "+anyo+" AND   "+
	"	       T_FPRD = "+mes+" and  "+
	"          T_AMNT <> 0 and "+
	"		   ARB2_TFGLD106.T_DCDT >= ARB2_CUENTAS.INIOPERACION AND "+
	"	       ARB2_TFGLD106.idtxconcilia is null "+
	" union all "+
	" SELECT  -1, "+
	"	      convert( datetime, convert( varchar, case when month(t_dcdt) = 12 then year(t_dcdt) + 1 else year(t_dcdt) end ) + '/' + convert( varchar, case when month(t_dcdt) = 12 then 1 else (month(t_dcdt)+1) end ) + '/01' )-1, "+
	"	      abs( dbo.fnc_totalDetalleConciliacion( dc.idtxconcilia, 'arb2_tfgld106' ) - dbo.fnc_totalDetalleConciliacion( dc.idtxconcilia, 'arb2_transacciones' ) ),  "+
	"	       isnull( dc.comentario, '' ) as observaciones, "+
	"	      'Concil Parcial ' + convert( varchar, dc.idtxconcilia ) + ' ' + convert( varchar, month(t_dcdt) ) + '/' + convert( varchar, year( t_dcdt ) ), "+
	"         0 as tipo "+
	" FROM 	  arb2_DETALLEconciliaciones dc  "+
	"		  inner join arb2_tfgld106 t "+
	"		  on t.recordid = ( "+
	"			 select	top 1 recordid "+
	"			 from 	arb2_tfgld106 "+
	"			 where	idtxconcilia = dc.idtxconcilia "+
	"		  ) "+
	" WHERE   dc.status = 0 AND  "+
	"	      dc.baanbanco = 2 and "+
	"	      dc.tipotransaccion = 2 and "+
	"	      dc.conciliacionid = (select conciliacionid from arb2_conciliaciones where cuentaid = "+cuentaid+" and mes = "+mes+" and anyo = "+anyo+") "+
	" order by 6 desc, 2";
sqlQuery[3] = 
    " select  ARB2_TRANSACCIONES.recordid," +
	"         fecha, "+
	"	      abs( importe ) as importe, "+
	"	       isnull( comentarios, '' ) as observaciones, "+
	"	      case when len(referencia) > 38 then substring(referencia, 0, 35) + '...' else referencia end as referencia, "+
	"          1 as tipo "+
	" from    ARB2_TRANSACCIONES , ARB2_CUENTAS  "+
	" where   tipo_transaccion = 0 and  "+
	"		  ARB2_CUENTAS.RECORDID = ARB2_TRANSACCIONES.CUENTAID AND "+
	"	      cuentaid = "+cuentaid+" and  "+
	"	      anyo = "+anyo+" AND   "+
	"	      periodo = "+mes+" and  "+
	"         importe <> 0 and "+
	"		   ARB2_TRANSACCIONES.FECHA >= ARB2_CUENTAS.INIOPERACION AND "+
	"	       ARB2_TRANSACCIONES.idtxconcilia is null "+
	" union all "+
	" SELECT  -1, "+
	"         convert( datetime, convert( varchar, case when month(fecha) = 12 then year(fecha) + 1 else year(fecha) end ) + '/' + convert( varchar, case when month(fecha) = 12 then 1 else (month(fecha)+1) end ) + '/01' )-1, "+
	"	      abs( dbo.fnc_totalDetalleConciliacion( dc.idtxconcilia, 'arb2_transacciones' ) - dbo.fnc_totalDetalleConciliacion( dc.idtxconcilia, 'arb2_tfgld106' ) ),  "+
	"	       isnull( dc.comentario, '' ) as observaciones, "+
	"	      'Concil Parcial ' + convert( varchar, dc.idtxconcilia ) + ' ' + convert( varchar, month(fecha) ) + '/' + convert( varchar, year( fecha ) ), "+
	"          0 as tipo "+
	" FROM 	  arb2_DETALLEconciliaciones dc  "+
	"		  inner join arb2_transacciones t "+
	"		  on t.recordid = ( "+
	"			 select	top 1 recordid "+
	"			 from 	arb2_transacciones "+
	"			 where	idtxconcilia = dc.idtxconcilia "+
	"		  ) "+
	" WHERE   dc.status = 0 AND  "+
	"	      dc.baanbanco = 1 and "+
	"	      dc.tipotransaccion = 2 and "+
	"	      dc.conciliacionid = (select conciliacionid from arb2_conciliaciones where cuentaid = "+cuentaid+" and mes = "+mes+" and anyo = "+anyo+") "+
	" order by 6 desc, 2 ";
sqlQuery[6] = 
	" SELECT  sum(T_AMNT)+ARB2_CUENTAS.BAANABONO as importe "+
	" from    ARB2_TFGLD106, ARB2_CUENTAS "+
	" where   ARB2_CUENTAS.cuentaBaan = ARB2_TFGLD106.T_LEAC AND "+
	"	      ARB2_CUENTAS.divisa = ARB2_TFGLD106.T_CCUR AND "+
	"	      T_DBCR = 2 and "+
	"	      T_LEAC in (select cuentabaan from arb2_cuentas where recordid = "+cuentaid+" ) and "+
	"	       T_FYER = "+anyo+" AND   "+
	"	       T_FPRD = "+mes+" and  "+
	"		   ARB2_TFGLD106.T_DCDT >= ARB2_CUENTAS.INIOPERACION AND "+
	"	       ARB2_TFGLD106.idtxconcilia is null "+
	" GROUP BY ARB2_CUENTAS.BAANABONO ";
sqlQuery[5] = 
	" SELECT  sum(importe)+ARB2_CUENTAS.BANCOABONO as importe "+
	" FROM    ARB2_TRANSACCIONES , ARB2_CUENTAS "+
	" where   ARB2_CUENTAS.RECORDID = ARB2_TRANSACCIONES.CUENTAID AND "+
	"	      tipo_transaccion = 1 and "+
	"	      cuentaid = "+cuentaid+" and "+
	"	      anyo = "+anyo+" AND   "+
	"	      periodo = "+mes+" and  "+
	"		   ARB2_TRANSACCIONES.FECHA >= ARB2_CUENTAS.INIOPERACION AND "+
	"	       ARB2_TRANSACCIONES.idtxconcilia is null "+
	" GROUP BY ARB2_CUENTAS.BANCOABONO";
sqlQuery[4] = 
	" SELECT  sum(T_AMNT)+ARB2_CUENTAS.BAANCARGO as importe "+
	" from    ARB2_TFGLD106, ARB2_CUENTAS "+
	" where   ARB2_CUENTAS.cuentaBaan = ARB2_TFGLD106.T_LEAC AND "+
	"	      ARB2_CUENTAS.divisa = ARB2_TFGLD106.T_CCUR AND "+
	"	      T_DBCR = 1 and "+
	"	      T_LEAC in (select cuentabaan from arb2_cuentas where recordid = "+cuentaid+" ) and "+
	"	       T_FYER = "+anyo+" AND   "+
	"	       T_FPRD = "+mes+" and  "+
	"		   ARB2_TFGLD106.T_DCDT >= ARB2_CUENTAS.INIOPERACION AND "+
	"	       ARB2_TFGLD106.idtxconcilia is null "+
	" GROUP BY ARB2_CUENTAS.BAANCARGO ";
sqlQuery[7] = 
	" SELECT  abs(sum(importe))+ARB2_CUENTAS.BANCOCARGO as importe "+
	" FROM    ARB2_TRANSACCIONES , ARB2_CUENTAS "+
	" where   ARB2_CUENTAS.RECORDID = ARB2_TRANSACCIONES.CUENTAID AND "+
	"	      tipo_transaccion = 0 and "+
	"	      cuentaid = "+cuentaid+" and "+
	"	      anyo = "+anyo+" AND   "+
	"	      periodo = "+mes+" and  "+
	"		   ARB2_TRANSACCIONES.FECHA >= ARB2_CUENTAS.INIOPERACION AND "+
	"	       ARB2_TRANSACCIONES.idtxconcilia is null "+
	" GROUP BY ARB2_CUENTAS.BANCOCARGO";
//Para formatear los numeros
NumberFormat nf = NumberFormat.getInstance(Locale.ENGLISH);
nf.setMinimumFractionDigits(2);
nf.setMaximumFractionDigits(2);
//---------Termina Variables Globales---------------------------------------------------

String cuenta__MMColParam = "1";
if (request.getParameter("cuentaID") !=null) {cuenta__MMColParam = (String)request.getParameter("cuentaID");}
String sqlCuenta = 
	" SELECT cuentabanco, "+
	"	     BANCOCARGO as importe4, "+
	"	     BANCOABONO as importe2, "+
	"	     BAANCARGO as importe1, "+
	"	     BAANABONO as importe3, "+
	"		 INIOPERACION "+
	" FROM   ARB2_CUENTAS "+
	" WHERE  RECORDID = "+cuenta__MMColParam;
Driver Drivercuenta = (Driver)Class.forName(MM_aranciaDB0_DRIVER).newInstance();
Connection Conncuenta = DriverManager.getConnection(MM_aranciaDB0_STRING,MM_aranciaDB0_USERNAME,MM_aranciaDB0_PASSWORD);
PreparedStatement Statementcuenta = Conncuenta.prepareStatement(sqlCuenta);
ResultSet cuenta = Statementcuenta.executeQuery();
boolean cuenta_isEmpty = !cuenta.next();
boolean cuenta_hasData = !cuenta_isEmpty;
Object cuenta_data;
int cuenta_numRows = 0;
response.setHeader("Content-disposition","inline; filename=\"-"+cuenta.getString("cuentabanco")+"-anexo_"+tipo+".pdf\"");
Driver Drivercounts = (Driver)Class.forName(MM_aranciaDB0_DRIVER).newInstance();
Connection Conncounts = DriverManager.getConnection(MM_aranciaDB0_STRING,MM_aranciaDB0_USERNAME,MM_aranciaDB0_PASSWORD);
PreparedStatement Statementcounts = Conncounts.prepareStatement(sqlQuery[(new Integer(tipo)).intValue()+3]);
ResultSet counts = Statementcounts.executeQuery();
boolean counts_isEmpty = !counts.next();
boolean counts_hasData = !counts_isEmpty;
Object counts_data;
int counts_numRows = 0;

Driver Drivertransacciones = (Driver)Class.forName(MM_aranciaDB0_DRIVER).newInstance();
Connection Conntransacciones = DriverManager.getConnection(MM_aranciaDB0_STRING,MM_aranciaDB0_USERNAME,MM_aranciaDB0_PASSWORD);
PreparedStatement Statementtransacciones = Conntransacciones.prepareStatement(sqlQuery[(new Integer(tipo)).intValue()-1]);
ResultSet transacciones = Statementtransacciones.executeQuery();
boolean transacciones_isEmpty = !transacciones.next();
boolean transacciones_hasData = !transacciones_isEmpty;
Object transacciones_data;
int transacciones_numRows = 0;

int Repeat1__numRows = -1;
int Repeat1__index = 0;
transacciones_numRows += Repeat1__numRows;

Document document = new Document(PageSize.LETTER);
ByteArrayOutputStream baosPDF = new ByteArrayOutputStream();
PdfWriter docWriter = null;

try{
  docWriter = PdfWriter.getInstance(document, baosPDF);

  //PdfWriter.getInstance(document, new FileOutputStream("cotizacion.pdf"));
  document.addTitle("Formato General de Reporte");
  document.addAuthor("Bank To Baan");
  document.open();
  
  //NOMBRE DEL REPORTE
  Table table = new Table(2);
  table.setPadding(0);
  table.setSpacing(0);
  table.setWidth(100);
  int cellsWidthT0[] = {100, 0};
  table.setWidths(cellsWidthT0);
  Cell cell;
  cell = new Cell( " "+title );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell(  );
  cell.setVerticalAlignment(Cell.ALIGN_TOP);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  document.add(table);
  
  //DETALLE DE LA CUENTA
  table = new Table(4);
  table.setPadding(0);
  table.setSpacing(0);
  table.setWidth(100);
  int cellsWidthT0DC[] = {33,33,34, 0};
  table.setWidths(cellsWidthT0DC);
  cell = new Cell( rr[4]+": " );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setHorizontalAlignment(Cell.ALIGN_RIGHT);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell( " "+(String)(((cuenta_data = cuenta.getObject("cuentabanco"))==null || cuenta.wasNull())?"":cuenta_data) );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell(  );
  cell.setVerticalAlignment(Cell.ALIGN_TOP);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell(  );
  cell.setVerticalAlignment(Cell.ALIGN_TOP);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell( rr[5]+": " );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setHorizontalAlignment(Cell.ALIGN_RIGHT);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell( " "+(dia[(new Integer(request.getParameter("mes"))).intValue()-1]==28?(new Integer(request.getParameter("anyo"))).intValue()%4==0?dia[(new Integer(request.getParameter("mes"))).intValue()-1]+1:dia[(new Integer(request.getParameter("mes"))).intValue()-1]:dia[(new Integer(request.getParameter("mes"))).intValue()-1])+"-"+request.getParameter("mes")+"-"+request.getParameter("anyo") );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell( " "+rr[6]+" "+tipo );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell(  );
  cell.setVerticalAlignment(Cell.ALIGN_TOP);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  document.add(table);
  
  Paragraph p = new Paragraph("CPIngredientes S.A. de C.V.");
  
  document.add(p);
  //LISTADO DE MOVIMIENTOS
  table = new Table(5);
  table.setPadding(0);
  table.setSpacing(0);
  table.setWidth(100);
  int cellsWidthT0MOV[] = {15, 17, 33, 35,  0};
  table.setWidths(cellsWidthT0MOV);
  /*cell = new Cell( rr[15]+" :" );
  cell.setBorder( 0 );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setHorizontalAlignment(Cell.ALIGN_RIGHT);
  table.addCell(cell);
  cell = new Cell( nf.format( cuenta.getDouble("importe"+tipo) ) );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setHorizontalAlignment(Cell.ALIGN_RIGHT);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell( " " );
  cell.setBorder( 0 );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  table.addCell(cell);
  cell = new Cell(  rr[16]+" : "+sdf.format( cuenta.getDate( "INIOPERACION" ) ) );
  cell.setBorder( 0 );
  cell.setVerticalAlignment(Cell.ALIGN_LEFT);
  table.addCell(cell);
  cell = new Cell(  );
  cell.setBorder( 0 );
  cell.setVerticalAlignment(Cell.ALIGN_TOP);
  table.addCell(cell);*/
  cell = new Cell( rr[7] );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setHorizontalAlignment(Cell.ALIGN_CENTER);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell( rr[8] );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setHorizontalAlignment(Cell.ALIGN_CENTER);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell( rr[9] );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setHorizontalAlignment(Cell.ALIGN_CENTER);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell( rr[14] );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setHorizontalAlignment(Cell.ALIGN_CENTER);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell(  );
  cell.setVerticalAlignment(Cell.ALIGN_TOP);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  
  double dbl_importe = 0;
  double dbl_total = 0;
  while ((transacciones_hasData)&&(Repeat1__numRows-- != 0)) { 
  	java.sql.Date fechaTmp = transacciones.getDate("fecha");
	String referenciaTmp   = transacciones.getString("referencia");
	dbl_importe = transacciones.getDouble("importe");
	dbl_total += dbl_importe;
	referenciaTmp = referenciaTmp!=null?referenciaTmp:"";
	cell = new Cell( sdf.format( fechaTmp ).toUpperCase( ) );
	cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
	cell.setHorizontalAlignment(Cell.ALIGN_CENTER);
	table.addCell(cell);
	cell = new Cell( nf.format( dbl_importe )+" " );
	cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
	cell.setHorizontalAlignment(Cell.ALIGN_RIGHT);
	table.addCell(cell);
	cell = new Cell( " "+referenciaTmp );
	cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
	cell.setHorizontalAlignment(Cell.ALIGN_LEFT);
	table.addCell(cell);
	cell = new Cell( " "+transacciones.getString("observaciones") );
	cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
	cell.setHorizontalAlignment(Cell.ALIGN_LEFT);
	table.addCell(cell);
	cell = new Cell(  );
	cell.setVerticalAlignment(Cell.ALIGN_TOP);
	table.addCell(cell);
	Repeat1__index++;
  	transacciones_hasData = transacciones.next();
  }
  cell = new Cell( "Total:" );
  cell.setBorder( 0 );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setHorizontalAlignment(Cell.ALIGN_RIGHT);
  table.addCell(cell);
  cell = new Cell( nf.format( dbl_total ) + " " );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  cell.setHorizontalAlignment(Cell.ALIGN_RIGHT);
  cell.setGrayFill(0.9f);
  table.addCell(cell);
  cell = new Cell( " " );
  cell.setBorder( 0 );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  table.addCell(cell);
  cell = new Cell( " " );
  cell.setBorder( 0 );
  cell.setVerticalAlignment(Cell.ALIGN_MIDDLE);
  table.addCell(cell);
  cell = new Cell(  );
  cell.setBorder( 0 );
  cell.setVerticalAlignment(Cell.ALIGN_TOP);
  table.addCell(cell);
  document.add(table);
  
  document.close();
  docWriter.close();
  
  ServletOutputStream  sout = response.getOutputStream();
  
  response.setContentLength(baosPDF.size());
  baosPDF.writeTo(sout);
  sout .flush();
}catch( DocumentException de ){
  System.err.println(de.getMessage());
}catch( IOException ioe ){
  System.err.println(ioe.getMessage());
}
//Cerrar queries
cuenta.close();
Statementcuenta.close();
Conncuenta.close();
counts.close();
Statementcounts.close();
Conncounts.close();
transacciones.close();
Statementtransacciones.close();
Conntransacciones.close();
%>